package ie.deloitte.cvapp.generateImportData;

import java.io.File;
import java.io.IOException;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.google.common.base.Splitter;
import com.google.common.io.Files;

public class ReadInSkills {

	public void bar() throws IOException, SQLException, ClassNotFoundException {

		Connection con = getConnection();
		int count = 0;
//		Statement stmt = con.createStatement();
//		stmt.execute("truncate Skillmatrix;");
//		System.out.println("Skillmatrix table emptied.");

//		Statement stmt2 = con.createStatement();
//		stmt2.execute("truncate employee_has_skill;");
//		System.out.println("employee_has_skill table emptied.");

		Map<String, Integer> skillsByName = new HashMap<String, Integer>();

		List<Skill> skills = selectAllFromDatabase();
		for (Skill skill : skills) {
			skillsByName.put(skill.name, skill.id);
		}

		File file = new File("skillsMatrix.csv");
		List<String> lines = Files.readLines(file, Charset.defaultCharset());

		List<Integer> skillIds = new ArrayList<Integer>();

		String query = "Insert into Skillmatrix(idSkill,name) values(?,?);";
		String query2 = "Insert into employee_has_skill(skill_idSkill,employee_idEmployee,level) values (?,?,?); ";
		PreparedStatement ps = con.prepareStatement(query);
		PreparedStatement ps2 = con.prepareStatement(query2);

		int i = 0;

		// first pass... figure out the IDs
		for (String line : lines) {
			for (String value : Splitter.on(",").split(line)) {
				// System.out.println(value);
				Integer skillId = skillsByName.get(value);
				skillIds.add(skillId);

				ps.clearParameters();
				ps.setInt(1, i++);
				ps.setString(2, value);
				ps.executeUpdate();
			}

			// skip processing rest of the line
			break;
		}

		boolean first = true;
		for (String line : lines) {
			if (first) {
				// skip first line
				first = false;
				continue;
			}

			int colNum = 0;
			int employeeId = 0;
			for (String value : Splitter.on(",").split(line)) {
				if (colNum++ == 0) {
					employeeId = Integer.parseInt(value);
				}

				// on a skill
				if (colNum >= 2 && !value.equals("")) {
					// Integer skillId = skillIds.get(colNum);
					// System.out.println(skillId);
					ps2.clearParameters();
					ps2.setInt(1, (colNum-1));
					ps2.setInt(2, employeeId);
					ps2.setString(3, value);
					ps2.executeUpdate();
					count++;
					// this is where we need to insert into the
					// employee_has_skill table.
					// insert into employee_has_skill(employee_id, skill_id)
					// values (employeeId, skillId)
					// ps.executeUpdate();
				}
			}
		}
		System.out.println(count + " rows added to employee_has_skill");
	}

	private int lookupEmployeeByName(String value) {
		return 1;
	}

	private List<Skill> selectAllFromDatabase() throws ClassNotFoundException,
			SQLException {
		Connection con = getConnection();

		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery("select * from Skillmatrix;");

		List list = new ArrayList();
		while (rs.next()) {
			System.out.println(rs.getRow());
			list.add(rs.getRow());
		}
		return list;
	}

	private Connection getConnection() throws ClassNotFoundException,
			SQLException {
		// load the JDBC driver
		Class.forName("com.mysql.jdbc.Driver");

		// get a connection to the database
		Connection con = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/javabootcamp", "root", "admin");
		return con;
	}

	public static void main(String[] args) throws ClassNotFoundException {
		ReadInSkills readSkills = new ReadInSkills();
		try {
			readSkills.bar();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

class Skill {
	int id;
	String name;
}
